package dao;

import model.Detail;
import model.LabTest;
import model.User;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: TM
 * Date: 11/19/12                                    t
 * Time: 10:40 PM
 * To change this template use File | Settings | File Templates.
 */
public class LabTestDaoImpl extends JdbcDaoSupport implements LabTestDao {

    public List<LabTest> viewLabTestList(User user) {
        try {
            String query = "SELECT \n" +
                    "NIC, PROFILE_NAME, INVOICE_NO, \n" +
                    "   HOSPITAL_CODE, TXN_DATE, STATUS\n" +
                    "FROM LAB_TEST WHERE NIC = ?";
            System.out.println(query);
            return (List<LabTest>) getJdbcTemplate().query(query, new Object[]{user.getNic()},
                    new RowMapper() {
                        public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                            LabTest labTest = new LabTest();
                            labTest.setNic(resultSet.getString("NIC"));
                            labTest.setInvoiceNo(resultSet.getString("INVOICE_NO"));
                            labTest.setProfile(resultSet.getString("PROFILE_NAME"));
                            labTest.setHospitalCode(resultSet.getString("HOSPITAL_CODE"));
                            labTest.setTxnDate(resultSet.getString("TXN_DATE"));

                            return labTest;
                        }

                    });
        } catch (EmptyResultDataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    public LabTest viewLabTestResult(User user, LabTest labTest) {
        try {
            String query = "SELECT \n" +
                    "NIC, PROFILE_NAME, INVOICE_NO, \n" +
                    "   HOSPITAL_CODE, TXN_DATE, STATUS\n" +
                    "FROM LAB_TEST WHERE NIC = ? AND INVOICE_NO = ? AND PROFILE_NAME = ?";
            System.out.println(query);
            labTest = (LabTest) getJdbcTemplate().queryForObject(query, new Object[]{user.getNic(), labTest.getInvoiceNo(), labTest.getProfile()},
                    new RowMapper() {
                        public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                            LabTest labTest = new LabTest();
                            labTest.setNic(resultSet.getString("NIC"));
                            labTest.setInvoiceNo(resultSet.getString("INVOICE_NO"));
                            labTest.setProfile(resultSet.getString("PROFILE_NAME"));
                            labTest.setHospitalCode(resultSet.getString("HOSPITAL_CODE"));
                            labTest.setTxnDate(resultSet.getString("TXN_DATE"));
                            return labTest;
                        }

                    });

            query = "SELECT \n" +
                    "NIC, INVOICE_NO, TEST_NAME, \n" +
                    "   RESULT, REFERENCE_RANGE, FLAG, \n" +
                    "   STATUS\n" +
                    "FROM LAB_TEST_BREAKDOWN WHERE NIC = ? AND INVOICE_NO = ?";
            labTest.setDetail((List<Detail>) getJdbcTemplate().query(query, new Object[]{user.getNic(), labTest.getInvoiceNo(), labTest.getProfile()},
                    new RowMapper() {
                        public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                            Detail detail = new Detail();
                            detail.setNic(resultSet.getString("NIC"));
                            detail.setInvoice(resultSet.getString("INVOICE_NO"));
                            detail.setTestName(resultSet.getString("TEST_NAME"));
                            detail.setResult(resultSet.getString("RESULT"));
                            detail.setReferenceRange(resultSet.getString("REFERENCE_RANGE"));
                            detail.setFlag(resultSet.getString("FLAG"));
                            return detail;
                        }

                    }));

            return labTest;
        } catch (EmptyResultDataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    public LabTest saveLabTestResult(User user, LabTest labTest) {
        try {
            String query = "INSERT INTO LAB_TEST (\n" +
                    "   NIC, PROFILE_NAME, INVOICE_NO, \n" +
                    "   HOSPITAL_CODE, TXN_DATE, STATUS) \n" +
                    "VALUES ( ?, ?, ?,\n" +
                    "    ?, sysdate, 1)";
            System.out.println(query);
            int raw = getJdbcTemplate().update(query, new Object[]{user.getNic(), labTest.getProfile(), labTest.getInvoiceNo(), labTest.getHospitalCode()});

            if (raw > 0) {
                List<Detail> detailList = labTest.getDetail();
                for (Detail detail : detailList) {
                    query = "INSERT INTO LAB_TEST_BREAKDOWN (\n" +
                            "   NIC, INVOICE_NO, TEST_NAME, \n" +
                            "   RESULT, REFERENCE_RANGE, FLAG, \n" +
                            "   STATUS) \n" +
                            "VALUES ( ?, ?, ?,\n" +
                            "    ?, ?, ?, )";
                    getJdbcTemplate().update(query, new Object[]{user.getNic(), labTest.getInvoiceNo(), detail.getTestName(), detail.getResult(), detail.getReferenceRange(), detail.getFlag()});
                }
            }
            return labTest;
        } catch (EmptyResultDataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

}
